﻿
/*
 ***************************************************************
 ** 上亿数量级分页存储过程(使用效果相当好，注意必须建立适当的索引) **
 ***************************************************************
 参数说明:
 1.Tables	   :表名称
 2.PrimaryKey  :主关键字
 3.Sort		   :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
 4.CurrentPage :当前页码(默认为：1)
 5.PageSize	   :分页尺寸（默认为：10）
 6.Filter	   :过滤语句，不带Where 
 7.Group	   :Group语句,不带Group By
 ***************************************************************
  ** Author:胡勇 QQ:80368704**
  ** CreateDate:2012-05-09  **
  ** 修改记录:
 ***************************************************************/
CREATE PROCEDURE [dbo].[pPaging]
    (
      @Tables VARCHAR(1000) ,
      @PrimaryKey VARCHAR(100) ,
      @Sort VARCHAR(200) = NULL ,
      @CurrentPage INT = 1 ,
      @PageSize INT = 10 ,
      @Fields VARCHAR(1000) = '*' ,
      @Filter VARCHAR(1000) = NULL ,
      @Group VARCHAR(1000) = NULL
    )
AS 
	SET NOCOUNT ON
    --如果没有指定排序字段，排序字段就默认与主键一至
    IF @Sort IS NULL OR @Sort = '' 
       SET @Sort = @PrimaryKey

    --得到主键@PrimaryKey的数据类型
    DECLARE @SortTable VARCHAR(100)
    DECLARE @SortName VARCHAR(100)
    DECLARE @strSortColumn VARCHAR(200)
    DECLARE @operator CHAR(2)
    DECLARE @type VARCHAR(100)
    DECLARE @prec INT

    --排序变量相关设置
    IF CHARINDEX('DESC', @Sort) > 0 
        BEGIN
            SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
            SET @operator = '<='
        END
    ELSE 
        BEGIN
            IF CHARINDEX('ASC', @Sort) = 0 
                SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
            SET @operator = '>='
        END


    IF CHARINDEX('.', @strSortColumn) > 0 
        BEGIN
            SET @SortTable = SUBSTRING(@strSortColumn, 0,
                                       CHARINDEX('.', @strSortColumn))
            SET @SortName = SUBSTRING(@strSortColumn,
                                      CHARINDEX('.', @strSortColumn) + 1,
                                      LEN(@strSortColumn))
        END
    ELSE 
        BEGIN
            SET @SortTable = @Tables
            SET @SortName = @strSortColumn
        END

    SELECT  @type = t.name ,
            @prec = c.prec
    FROM    sysobjects o
            JOIN syscolumns c ON o.id = c.id
            JOIN systypes t ON c.xusertype = t.xusertype
    WHERE   o.name = @SortTable
            AND c.name = @SortName

    IF CHARINDEX('char', @type) > 0 
        SET @type = @type + '(' + CAST(@prec AS VARCHAR) + ')'

    DECLARE @strPageSize VARCHAR(50)
    DECLARE @strStartRow VARCHAR(50)
    DECLARE @strFilter VARCHAR(1000)
    DECLARE @strSimpleFilter VARCHAR(1000)
    DECLARE @strGroup VARCHAR(1000)

    --当前页<1，则设置为第一页
    IF @CurrentPage < 1 
        SET @CurrentPage = 1

    --设置分页变量
    SET @strPageSize = CAST(@PageSize AS VARCHAR(50))
    SET @strStartRow = CAST(( ( @CurrentPage - 1 ) * @PageSize + 1 ) AS VARCHAR(50))

    --设置Where条件与分组变量
    IF @Filter IS NOT NULL AND @Filter != '' 
        BEGIN
            SET @strFilter = ' WHERE ' + @Filter + ' '
            SET @strSimpleFilter = ' AND ' + @Filter + ' '
        END
    ELSE 
        BEGIN
            SET @strSimpleFilter = ''
            SET @strFilter = ''
        END
        
    IF @Group IS NOT NULL
        AND @Group != '' 
        SET @strGroup = ' GROUP BY ' + @Group + ' '
    ELSE 
        SET @strGroup = ''
    
    --此处应用SET ROWCOUNT提高效率，这就是优于其他分页存储过程的地方
    EXEC(
    '
    DECLARE @SortColumn ' + @type + '
    SET ROWCOUNT ' + @strStartRow + '
    SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
    SET ROWCOUNT ' + @strPageSize + '
    SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
    SET ROWCOUNT 0
    '
    )
    
    SET NOCOUNT OFF


